﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace Von.Library
{
    /// <summary>
    /// 有关数据库操作或语句生成的函数库
    /// </summary>
    public static class SqlCondition
    {
        /// <summary>
        /// 清除sql表达式中项目含有空格的风险，如果有空格就有可能是嵌入语句
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        private static string trimField(string s)
        {
            string result = s.TrimStart(' ');
            int pos = result.IndexOf(' ');
            if (pos > 0) result = result.Remove(pos);
            if (result[0] == '\'' && !result.EndsWith("\'"))
                return result + '\'';
            return result;
        }
        // 
        /// <summary>
        /// 按照预定格式录入查询条件信息，自动转换为SQL查询语句的函数
        /// </summary>
        /// <param name="conditionStr">查询条件信息，例如：{'A','in',123,434,34,54}{'C','>',12}{'D','<=','qwe'}{'D','LeftLIKE', 'qw3e23'}</param>
        /// <returns>SQL查询语句</returns>
        public static string SimpleConditionToSql(string conditionStr)
        {
            string[] items = conditionStr.Split(new char[] { '}' }, StringSplitOptions.RemoveEmptyEntries);
            string result = "";
            foreach (string item in items)
            {
                string[] exp = item.TrimStart('{').Split(',');
                if (exp[1].Equals("=", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("==", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("等于", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + "=" + trimField(exp[2]);
                else if (exp[1].Equals("!=", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("<>", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("不等于", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + "<>" + trimField(exp[2]);
                else if (exp[1].Equals("<", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("小于", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + "<" + trimField(exp[2]);
                else if (exp[1].Equals("<=", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("=<", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("小于等于", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + "<=" + trimField(exp[2]);
                else if (exp[1].Equals(">", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("大于", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + ">" + trimField(exp[2]);
                else if (exp[1].Equals(">=", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("=>", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("大于等于", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + ">=" + trimField(exp[2]);
                else if (exp[1].Equals("llike", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("left like", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("leftlike", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + " LIKE '" + trimField(exp[2]).Trim('\'') + "%'";
                else if (exp[1].Equals("rlike", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("right like", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("rightlike", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + " LIKE '%" + trimField(exp[2]).Trim('\'') + "'";
                else if (exp[1].Equals("like", StringComparison.OrdinalIgnoreCase) || exp[1].Equals("近似", StringComparison.OrdinalIgnoreCase))
                    result += trimField(exp[0]) + " LIKE '%" + trimField(exp[2]).Trim('\'') + "%'";
                else if (exp[1].Equals("in", StringComparison.OrdinalIgnoreCase))
                {
                    result += trimField(exp[0]) + " IN (";
                    for (int i = 2; i < exp.Length; i++)
                    {
                        result += trimField(exp[i]) + ",";
                    }
                    result = result.Remove(result.Length - 1) + ")";
                }
                else return "UNKNOW " + exp[1];
                if (conditionStr.Contains("A.Note"))
                {
                    result += " OR  ";
                }
                else
                {
                    result += " AND ";
                }
            }
            return result.Remove(result.Length - 5);
        }

    }
    public static class SqlHelper
    {
        public static Dictionary<string, object> readData(IDataReader reader) {
            Dictionary<string, object> data = new Dictionary<string, object>();
            if (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++){
                    data.Add(reader[i].ToString(), reader.GetValue(i));
                }
            }
            reader.Close();
            return data;
        }
        public static List<Dictionary<string, object>> readList(IDataReader reader)
        {
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            if (reader.Read())
            {
                Dictionary<string, object> data = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    data.Add(reader.GetName(i).ToString(), reader.GetValue(i));
                }
                list.Add(data);
            }
            reader.Close();
            return list;
        }
    }
}
